<?php

namespace app\order\logic;

use think\facade\Db;
use think\facade\Cache;
use util\Excel;
use think\exception\ValidateException;
use think\facade\Log;
use util\Token;
use util\Upload;
use think\cache\driver\Redis;
use app\api\logic\ApiOrderLogic;

class OrderReportDailyLogic
{

    public function __construct()
    {

    }

    /**
     * @param $params
     * @return \think\response\Json
     * @throws \think\db\exception\DbException
     * @desc 列表
     * @author zhangheng
     */
    public function index($params = [])
    {
        //实时数据
        $data = $this->RealTimeToday($params);
        return json(DefaultResponse(200, true, '获取成功', $data));
    }

    /**
     * @param $params
     * @return \think\response\Json
     * @throws \think\db\exception\DbException
     * @desc 导出
     * @author zhangheng
     */
    public function export($params = [])
    {
        $limit   = $params['limit']   ??  10;
        $shop_id = $params['shop_id'] ??  0;
        $adminId = $params['adminId'] ??  '';
        $groupId = $params['groupId'] ??  '';

        //实时时间搜索
        $date = $params['date'] ?? "";
        $customDate = $params['customDate'] ?? 0;

        $where = [];
        $where_campaign = [];
        $where_total = [];
        $where_premium = [];

        //德国汇率
        $ApiOrderLogic = new ApiOrderLogic();
        $euroToUsdRate = $ApiOrderLogic->RealTimeExchangeRateInquiry($params);

        //customDate优先
        if(!empty($customDate)){
            switch ($customDate) {
                case '1':
                    //实时
                    $yesterday = date('Y-m-d', time() - timezone());  
                    $midnightYesterday = strtotime($yesterday . ' 00:00:00');
                    $endOfYesterday  = strtotime($yesterday . ' 23:59:59');
                    $where[]  = ['a.createtime', '>=', $midnightYesterday];
                    $where[]  = ['a.createtime', '<=', $endOfYesterday]; 
                    $where_premium[]  = ['b.createtime', '>=', $midnightYesterday];
                    $where_premium[]  = ['b.createtime', '<=', $endOfYesterday]; 

                    $where_campaign[] = ['a.stat_time', '>=', $midnightYesterday];
                    $where_campaign[] = ['a.stat_time', '<=', $endOfYesterday];

                    $where_total[]  = ['stat_time', '>=', $midnightYesterday];
                    $where_total[]  = ['stat_time', '<=', $endOfYesterday]; 

                    break;
                case '2':
                    $yesterday = date('Y-m-d', strtotime('-1 day') - timezone());
                    $midnightYesterday = strtotime($yesterday . ' 00:00:00');
                    $endOfYesterday  = strtotime($yesterday . ' 23:59:59');
                    $where[]  = ['a.createtime', '>=', $midnightYesterday];
                    $where[]  = ['a.createtime', '<=', $endOfYesterday]; 
                    $where_premium[]  = ['b.createtime', '>=', $midnightYesterday];
                    $where_premium[]  = ['b.createtime', '<=', $endOfYesterday]; 

                    $where_campaign[] = ['a.stat_time', '>=', $midnightYesterday];
                    $where_campaign[] = ['a.stat_time', '<=', $endOfYesterday];

                    $where_total[]  = ['stat_time', '>=', $midnightYesterday];
                    $where_total[]  = ['stat_time', '<=', $endOfYesterday]; 

                    break;
                case '3':
                    $endOfYesterday = strtotime(date('Y-m-d', time() - timezone()) . ' 23:59:59');
                    $midnightYesterday = strtotime(date('Y-m-d', strtotime('-6 day') - timezone()) . ' 00:00:00');
                    $where[] = ['a.createtime', '<=', $endOfYesterday];
                    $where[] = ['a.createtime', '>=', $midnightYesterday];
                    $where_premium[] = ['b.createtime', '<=', $endOfYesterday];
                    $where_premium[] = ['b.createtime', '>=', $midnightYesterday];

                    $where_campaign[] = ['a.stat_time', '>=', $midnightYesterday];
                    $where_campaign[] = ['a.stat_time', '<=', $endOfYesterday];

                    $where_total[]  = ['stat_time', '>=', $midnightYesterday];
                    $where_total[]  = ['stat_time', '<=', $endOfYesterday]; 

                    break;
                default:
                    $yesterday = date('Y-m-d', strtotime('-1 day') - timezone());
                    $midnightYesterday = strtotime($yesterday . ' 00:00:00');
                    $endOfYesterday  = strtotime($yesterday . ' 23:59:59');
                    $where[]  = ['a.createtime', '>=', $midnightYesterday];
                    $where[]  = ['a.createtime', '<=', $endOfYesterday]; 
                    $where_premium[]  = ['b.createtime', '>=', $midnightYesterday];
                    $where_premium[]  = ['b.createtime', '<=', $endOfYesterday]; 

                    $where_campaign[] = ['a.stat_time', '>=', $midnightYesterday];
                    $where_campaign[] = ['a.stat_time', '<=', $endOfYesterday];

                    $where_total[]  = ['stat_time', '>=', $midnightYesterday];
                    $where_total[]  = ['stat_time', '<=', $endOfYesterday]; 
            }
        }else{
            // 当自定义时间为空时 查日期有没有值
            if (!empty($date)) {
                $midnightYesterday = strtotime($date['0']);
                $endOfYesterday  = strtotime($date['1']) + 3600*24-1;
                $where[] = ['a.createtime', '>=', $midnightYesterday];
                $where[] = ['a.createtime', '<=', $endOfYesterday];
                $where_premium[] = ['b.createtime', '>=', strtotime($date['0'])];
                $where_premium[] = ['b.createtime', '<=', strtotime($date['1']) + 3600*24-1];

                $where_campaign[] = ['a.stat_time', '>=', strtotime($date['0'])];
                $where_campaign[] = ['a.stat_time', '<=', strtotime($date['1']) + 3600*24-1];

                $where_total[] = ['stat_time', '>=', strtotime($date['0'])];
                $where_total[] = ['stat_time', '<=', strtotime($date['1']) + 3600*24-1];

            }else{
                $yesterday = date('Y-m-d', strtotime('-1 day') - timezone());
                $midnightYesterday = strtotime($yesterday . ' 00:00:00');
                $endOfYesterday  = strtotime($yesterday . ' 23:59:59');
                $where[]  = ['a.createtime', '>=', $midnightYesterday];
                $where[]  = ['a.createtime', '<=', $endOfYesterday]; 
                $where_premium[]  = ['b.createtime', '>=', $midnightYesterday];
                $where_premium[]  = ['b.createtime', '<=', $endOfYesterday];  

                $where_campaign[]  = ['a.stat_time', '>=', $midnightYesterday];
                $where_campaign[]  = ['a.stat_time', '<=', $endOfYesterday]; 
                
                $where_total[]  = ['stat_time', '>=', $midnightYesterday];
                $where_total[]  = ['stat_time', '<=', $endOfYesterday]; 
            }
        }

        $where[]  = ['a.financial_status', 'in', ["partially_paid", "paid", "partially_refunded", "refunded"]];
        
        //负责人可以查看的店铺
        $shop_map = $this->PersonInCanView($adminId);
        if(!empty($shop_map)){
            $where[] = ['a.shop_id', 'in', $shop_map];
        }else{
            if (!empty($shop_id)) {
                $where[] = ['a.shop_id', '=', $shop_id];
            }
        }

        //2024-11-12  运营查看自己账号;
        if(in_array($groupId,[11,17,15,20])){
            $where[] = ['p.admin_id', 'like', '%' . $adminId . '%'];
            if($adminId==11){
                $where[] = ['p.admin_id', '<>',111];
            }
        }

        $list = Db::name('order_achievement')
            ->field('
                COUNT(a.id) as quantity,
                sum(a.total_price) as total_price,
                sum(a.old_total_price) as old_total_price,
                a.shop_id,
                a.currency,
                p.admin_id,
                p.name as shop_name,
                sum(a.skuquantity) as skuquantity,
                sum(a.freight) as freight,
                sum(a.estimate_freight) as estimate_freight,
                sum(a.material) as material
            ')
            ->alias('a')
            ->leftJoin('shops p', 'p.id = a.shop_id')
            ->where($where)
            ->group('a.shop_id')
            ->paginate($limit);
        $lists = $list->items();
        
        //汇率-----使用最新的汇率
        $order_exchange = Db::name('order_exchange')->order('date desc')->find();

        //面板广告费
        $where_campaign[] = ['a.spend', '>=', 0.001];       
        $roi_total = 0;
        $advertising_total = 0;
        $total_price_total = 0;
        $quantity_total = 0;
        $skuquantity_total = 0;
        $customer_price_total = 0;
        $customer_sku_total = 0;

    if (empty($shop_id)) {
        //查看广告费
        $advertising_shop = Db::name('advertising')
            ->field('sum(a.spend) as spend,shop_id')
            ->alias('a')
            ->where($where_campaign)
            ->group("shop_id")
            ->order("spend desc")
            ->select()->toarray();
        $advertising_shop_map = array_column($advertising_shop,'shop_id');
        $shops = Db::name('shops')->field('id,name')->where('id','in',$advertising_shop_map)->select()->toarray();
        $shops_map = array_column($shops,'name','id');
        
        $advertising_shopspend_map = array_column($advertising_shop,'spend','shop_id');
        if($lists){
            foreach ($lists as $k=>$v) {
                if(in_array($v['shop_id'],$advertising_shop_map)){
                    unset($advertising_shopspend_map[$v['shop_id']]);
                }
            }
        }
        $list_advs = [];
        if(!empty($advertising_shopspend_map)){
            foreach ($advertising_shopspend_map as $k=>$v) {
                //加权限 某些角色不能查看
                if(in_array($groupId,[11,17,15,20])){
                    $shops_admin = Db::name('shops')->field('admin_id')->where('id',$k)->find();
                    $shops_admins = $shops_admin['admin_id'] ?? [];
                    if(!empty($shops_admins)){
                        $adminId_arr = explode(',',$shops_admins);
                        if(!in_array($adminId,$adminId_arr)){
                            continue;
                        }
                    }else{
                        continue;
                    }
                    if($k == 0){
                        continue;
                    }
                }
                //加权限 某些角色不能查看 结束
                $list_adv = [];
                $list_adv['advertising'] = (float)$v;
                $advertising_total += (float)$v;
                $list_adv['profit'] = number_format(0 - (float)$v, 2, '.', '');
                $list_adv['quantity'] = 0;
                $list_adv['total_price'] = 0;
                $list_adv['skuquantity'] = 0;
                $list_adv['customer_price'] = 0;
                $list_adv['customer_sku'] = 0;
                $list_adv['roi'] = 0;
                $list_adv['LogisticsMarkupFee'] = 0;
                $list_adv['estimate_freight'] = 0;
                $list_adv['material'] = 0;
                $list_adv['premium_price'] = 0;
                $list_adv['PlatformCommission'] = 0;
                $list_adv['refund'] = 0;
                $list_adv['shop_id'] = $k;
                $list_adv['shop_name'] = $shops_map[$k] ?? "等待分配";
                $list_advs[] = $list_adv;
            }
        }
        // halt($list_advs);
    }else{
        $list_advs = [];
    }
        //查看广告费
        if($lists){
            foreach ($lists as &$v) {
                $user_id        = $v['admin_id']??'';
                $adminNames     = self::get_admin_name($user_id);
                $v['admin_name']= $adminNames;
                $v['admin_id']  = $user_id ? explode(',',$user_id) : '';
                //打包费
                $v['LogisticsMarkupFee'] = getRes($v['quantity']*$order_exchange['LogisticsMarkupFee']);

                $v['total_price'] = $v['old_total_price'];

                switch ($v['currency']) {
                    case 'USD':
                        break;
                    case 'EUR':
                        $v['total_price'] = (float)number_format((float)$v['total_price'] * (float)$euroToUsdRate, 2, '.', '');
                        break;
                    default:
                }
                $v['estimate_freight'] = getRes($v['estimate_freight']/$order_exchange['dollar']);
                //预估物流--人民币转美金
                $v['freight']  =  getRes($v['freight']/$order_exchange['dollar']);
                //预估采购
                $v['material'] =  getRes($v['material']/$order_exchange['dollar']);
                //广告费
                $v['advertising'] = $this->advertising_campaign($where_campaign,$v['shop_id']);
                //退款
                $v['refund'] = (float)number_format((float)$v['total_price'] * 0.03, 2, '.', '');
                //平台佣金(预估6个点)
                $v['PlatformCommission'] = (float)number_format((float)$v['total_price'] * 0.06, 2, '.', '');
                //ROI
                if ($v['advertising'] > 0.001 && $v['total_price'] > 0.001) {
                    $v['roi'] = number_format((float)$v['total_price'] / (float)$v['advertising'], 2, '.', '');
                }else{
                    $v['roi'] = 0;
                }
                //保险费
                $where_premium['b.shop_id'] = $v['shop_id'];
                $premium_price = Db::name('order_info')
                    ->field('sum(a.price) as premium_price')
                    ->alias('a')
                    ->join('qu_order_achievement b','b.order_id = a.order_id', 'left')
                    ->where($where_premium)
                    ->where('a.is_seel',1)
                    ->select()
                    ->toarray();
                $v['premium_price'] = $premium_price[0]['premium_price']??0;
                //盈利
                $v['profit'] = number_format((float)$v['total_price'] - (float)$v['advertising'] - (float)$v['estimate_freight'] - (float)$v['material'] - (float)$v['refund'] - (float)$v['PlatformCommission'] - (float)$v['premium_price'], 2, '.', '');

                //计算ROI
                $advertising_total += (float)$v['advertising'];
                $total_price_total += (float)$v['total_price'];
                $quantity_total += (float)$v['quantity'];
                $skuquantity_total += (float)$v['skuquantity'];
                $v['skuquantity'] = (int)$v['skuquantity'];

                //客单价 = 店铺每日总销售额/店铺的每日单量
                $v['customer_price'] = number_format((float)$v['total_price'] / (float)$v['quantity'], 2, '.', '');
                // 客单件 = 店铺每日SKU件数/店铺的每日单量
                $v['customer_sku'] = number_format((float)$v['skuquantity'] / (float)$v['quantity'], 2, '.', '');
            }

            $lists = array_merge($lists,$list_advs);
        }else{
            $lists = $list_advs;
        }
        
        $header = [];
        $header[] = ['店铺', 'shop_name'];
        $header[] = ['销售额$', 'total_price'];
        $header[] = ['广告费$', 'advertising'];
        $header[] = ['单量', 'quantity'];
        $header[] = ['SKU件数', 'skuquantity'];
        $header[] = ['客单价$', 'customer_price'];
        $header[] = ['客单件', 'customer_sku'];
        $header[] = ['ROI', 'roi'];
        $header[] = ['打包费$', 'LogisticsMarkupFee'];
        $header[] = ['预估物流$', 'estimate_freight'];
        $header[] = ['采购成本$', 'material'];
        $header[] = ['保险费$', 'premium_price'];
        $header[] = ['平台佣金$', 'PlatformCommission'];
        $header[] = ['预估退款$', 'refund'];
        $header[] = ['预估盈利$', 'profit'];
        $header[] = ['运营', 'admin_name'];
        
        $tableName = "OrderReportDaily";

        $fileName = "export_" . $tableName . "_" . time();
        return Excel::exportData($lists, $header, $fileName, 'xlsx');
    }
    
    /**
     * @desc    实时记录今天业绩
     * @date    2024/7/16
     * @author  zhangheng
     */
    public function RealTimeToday($params)
    {
        $limit   = $params['limit']   ??  10;
        $shop_id = $params['shop_id'] ??  0;
        $adminId = $params['adminId'] ??  '';
        $groupId = $params['groupId'] ??  '';

        //实时时间搜索
        $date = $params['date'] ?? "";
        $customDate = $params['customDate'] ?? 0;

        $where = [];
        $where_campaign = [];
        $where_total = [];
        $where_premium = [];

        //德国汇率
        $ApiOrderLogic = new ApiOrderLogic();
        $euroToUsdRate = $ApiOrderLogic->RealTimeExchangeRateInquiry($params);

        //customDate优先
        if(!empty($customDate)){
            switch ($customDate) {
                case '1':
                    //实时
                    $yesterday = date('Y-m-d', time() - timezone());  
                    $midnightYesterday = strtotime($yesterday . ' 00:00:00');
                    $endOfYesterday  = strtotime($yesterday . ' 23:59:59');
                    $where[]  = ['a.createtime', '>=', $midnightYesterday];
                    $where[]  = ['a.createtime', '<=', $endOfYesterday]; 
                    $where_premium[]  = ['b.createtime', '>=', $midnightYesterday];
                    $where_premium[]  = ['b.createtime', '<=', $endOfYesterday]; 

                    $where_campaign[] = ['a.stat_time', '>=', $midnightYesterday];
                    $where_campaign[] = ['a.stat_time', '<=', $endOfYesterday];

                    $where_total[]  = ['stat_time', '>=', $midnightYesterday];
                    $where_total[]  = ['stat_time', '<=', $endOfYesterday]; 

                    break;
                case '2':
                    $yesterday = date('Y-m-d', strtotime('-1 day') - timezone());
                    $midnightYesterday = strtotime($yesterday . ' 00:00:00');
                    $endOfYesterday  = strtotime($yesterday . ' 23:59:59');
                    $where[]  = ['a.createtime', '>=', $midnightYesterday];
                    $where[]  = ['a.createtime', '<=', $endOfYesterday]; 
                    $where_premium[]  = ['b.createtime', '>=', $midnightYesterday];
                    $where_premium[]  = ['b.createtime', '<=', $endOfYesterday]; 

                    $where_campaign[] = ['a.stat_time', '>=', $midnightYesterday];
                    $where_campaign[] = ['a.stat_time', '<=', $endOfYesterday];

                    $where_total[]  = ['stat_time', '>=', $midnightYesterday];
                    $where_total[]  = ['stat_time', '<=', $endOfYesterday]; 

                    break;
                case '3':
                    $endOfYesterday = strtotime(date('Y-m-d', time() - timezone()) . ' 23:59:59');
                    $midnightYesterday = strtotime(date('Y-m-d', strtotime('-6 day') - timezone()) . ' 00:00:00');
                    $where[] = ['a.createtime', '<=', $endOfYesterday];
                    $where[] = ['a.createtime', '>=', $midnightYesterday];
                    $where_premium[] = ['b.createtime', '<=', $endOfYesterday];
                    $where_premium[] = ['b.createtime', '>=', $midnightYesterday];

                    $where_campaign[] = ['a.stat_time', '>=', $midnightYesterday];
                    $where_campaign[] = ['a.stat_time', '<=', $endOfYesterday];

                    $where_total[]  = ['stat_time', '>=', $midnightYesterday];
                    $where_total[]  = ['stat_time', '<=', $endOfYesterday]; 

                    break;
                default:
                    $yesterday = date('Y-m-d', strtotime('-1 day') - timezone());
                    $midnightYesterday = strtotime($yesterday . ' 00:00:00');
                    $endOfYesterday  = strtotime($yesterday . ' 23:59:59');
                    $where[]  = ['a.createtime', '>=', $midnightYesterday];
                    $where[]  = ['a.createtime', '<=', $endOfYesterday]; 
                    $where_premium[]  = ['b.createtime', '>=', $midnightYesterday];
                    $where_premium[]  = ['b.createtime', '<=', $endOfYesterday]; 

                    $where_campaign[] = ['a.stat_time', '>=', $midnightYesterday];
                    $where_campaign[] = ['a.stat_time', '<=', $endOfYesterday];

                    $where_total[]  = ['stat_time', '>=', $midnightYesterday];
                    $where_total[]  = ['stat_time', '<=', $endOfYesterday]; 
            }
        }else{
            // 当自定义时间为空时 查日期有没有值
            if (!empty($date)) {
                $midnightYesterday = strtotime($date['0']);
                $endOfYesterday  = strtotime($date['1']) + 3600*24-1;
                $where[] = ['a.createtime', '>=', $midnightYesterday];
                $where[] = ['a.createtime', '<=', $endOfYesterday];
                $where_premium[] = ['b.createtime', '>=', strtotime($date['0'])];
                $where_premium[] = ['b.createtime', '<=', strtotime($date['1']) + 3600*24-1];

                $where_campaign[] = ['a.stat_time', '>=', strtotime($date['0'])];
                $where_campaign[] = ['a.stat_time', '<=', strtotime($date['1']) + 3600*24-1];

                $where_total[] = ['stat_time', '>=', strtotime($date['0'])];
                $where_total[] = ['stat_time', '<=', strtotime($date['1']) + 3600*24-1];

            }else{
                $yesterday = date('Y-m-d', strtotime('-1 day') - timezone());
                $midnightYesterday = strtotime($yesterday . ' 00:00:00');
                $endOfYesterday  = strtotime($yesterday . ' 23:59:59');
                $where[]  = ['a.createtime', '>=', $midnightYesterday];
                $where[]  = ['a.createtime', '<=', $endOfYesterday]; 
                $where_premium[]  = ['b.createtime', '>=', $midnightYesterday];
                $where_premium[]  = ['b.createtime', '<=', $endOfYesterday];  

                $where_campaign[]  = ['a.stat_time', '>=', $midnightYesterday];
                $where_campaign[]  = ['a.stat_time', '<=', $endOfYesterday]; 
                
                $where_total[]  = ['stat_time', '>=', $midnightYesterday];
                $where_total[]  = ['stat_time', '<=', $endOfYesterday]; 
            }
        }

        $where[]  = ['a.financial_status', 'in', ["partially_paid", "paid", "partially_refunded", "refunded"]];

        if($shop_id){
            $where[] = ['a.shop_id', '=', $shop_id];
        }
        $where_campaign2 = [];
        //2024-11-12  运营查看自己账号;
        if(in_array($groupId,[11,17,15,20,19])){
            $where[] = ['p.admin_id', 'like', '%' . $adminId . '%'];
            if($adminId==11){
                $where[] = ['p.admin_id', '<>',111];
            }
            $where_campaign2[] = ['a.admin_id', '=',$adminId];
        }
        // $where[] = ['p.status', '<>',2];

        $list = Db::name('order_achievement')
            ->field('
                COUNT(a.id) as quantity,
                sum(a.total_price) as total_price,
                sum(a.old_total_price) as old_total_price,
                a.shop_id,
                a.currency,
                p.admin_id,
                p.name as shop_name,
                sum(a.skuquantity) as skuquantity,
                sum(a.freight) as freight,
                sum(a.estimate_freight) as estimate_freight,
                sum(a.material) as material
            ')
            ->alias('a')
            ->leftJoin('shops p', 'p.id = a.shop_id')
            ->where($where)
            ->group('a.shop_id')
            ->order('quantity desc')
            ->paginate($limit);
        $lists = $list->items();
        
        //汇率-----使用最新的汇率
        $order_exchange = Db::name('order_exchange')->order('date desc')->find();

        //面板广告费
        $where_campaign[] = ['a.spend', '>=', 0.001];       
        $roi_total = 0;
        $advertising_total = 0;
        $total_price_total = 0;
        $quantity_total = 0;
        $skuquantity_total = 0;
        $customer_price_total = 0;
        $customer_sku_total = 0;

    if (empty($shop_id)) {
        //查看广告费
        $advertising_shop = Db::name('advertising')
            ->field('sum(a.spend) as spend,shop_id')
            ->alias('a')
            ->where($where_campaign)
            ->where($where_campaign2)
            ->group("shop_id")
            ->order("spend desc")
            ->select()->toarray();
        $advertising_shop_map = array_column($advertising_shop,'shop_id');
        $shops = Db::name('shops')->field('id,name')->where('id','in',$advertising_shop_map)->select()->toarray();
        $shops_map = array_column($shops,'name','id');
        
        $advertising_shopspend_map = array_column($advertising_shop,'spend','shop_id');
        if($lists){
            foreach ($lists as $k=>$v) {
                if(in_array($v['shop_id'],$advertising_shop_map)){
                    unset($advertising_shopspend_map[$v['shop_id']]);
                }
            }
        }
        $list_advs = [];
        if(!empty($advertising_shopspend_map)){
            foreach ($advertising_shopspend_map as $k=>$v) {
                //加权限 某些角色不能查看
                if(in_array($groupId,[11,17,15,20])){
                    $shops_admin = Db::name('shops')->field('admin_id')->where('id',$k)->find();
                    $shops_admins = $shops_admin['admin_id'] ?? [];
                    if(!empty($shops_admins)){
                        $adminId_arr = explode(',',$shops_admins);
                        if(!in_array($adminId,$adminId_arr)){
                            continue;
                        }
                    }else{
                        continue;
                    }
                    if($k == 0){
                        continue;
                    }
                }
                //加权限 某些角色不能查看 结束
                $list_adv = [];
                $list_adv['advertising'] = (float)$v;
                $advertising_total += (float)$v;
                $list_adv['profit'] = number_format(0 - (float)$v, 2, '.', '');
                $list_adv['quantity'] = 0;
                $list_adv['total_price'] = 0;
                $list_adv['skuquantity'] = 0;
                $list_adv['customer_price'] = 0;
                $list_adv['customer_sku'] = 0;
                $list_adv['roi'] = 0;
                $list_adv['LogisticsMarkupFee'] = 0;
                $list_adv['estimate_freight'] = 0;
                $list_adv['material'] = 0;
                $list_adv['premium_price'] = 0;
                $list_adv['PlatformCommission'] = 0;
                $list_adv['refund'] = 0;
                $list_adv['shop_id'] = $k;
                $list_adv['shop_name'] = $shops_map[$k] ?? "等待分配";
                $list_advs[] = $list_adv;
            }
        }
        // halt($list_advs);
    }else{
        $list_advs = [];
    }
        //查看广告费
        if($lists){
            foreach ($lists as &$v) {
                $user_id        = $v['admin_id']??'';
                $adminNames     = self::get_admin_name($user_id);
                $v['admin_name']= $adminNames;
                $v['admin_id']  = $user_id ? explode(',',$user_id) : '';
                //打包费
                $v['LogisticsMarkupFee'] = getRes($v['quantity']*$order_exchange['LogisticsMarkupFee']);

                $v['total_price'] = $v['old_total_price'];

                switch ($v['currency']) {
                    case 'USD':
                        break;
                    case 'EUR':
                        $v['total_price'] = (float)number_format((float)$v['total_price'] * (float)$euroToUsdRate, 2, '.', '');
                        break;
                    default:
                }
                $v['estimate_freight'] = getRes($v['estimate_freight']/$order_exchange['dollar']);
                //预估物流--人民币转美金
                $v['freight']  =  getRes($v['freight']/$order_exchange['dollar']);
                //预估采购
                $v['material'] =  getRes($v['material']/$order_exchange['dollar']);
                //广告费
                $v['advertising'] = $this->advertising_campaign($where_campaign,$v['shop_id']);
                //退款
                $v['refund'] = (float)number_format((float)$v['total_price'] * 0.03, 2, '.', '');
                //平台佣金(预估6个点)
                $v['PlatformCommission'] = (float)number_format((float)$v['total_price'] * 0.06, 2, '.', '');
                //ROI
                if ($v['advertising'] > 0.001 && $v['total_price'] > 0.001) {
                    $v['roi'] = number_format((float)$v['total_price'] / (float)$v['advertising'], 2, '.', '');
                }else{
                    $v['roi'] = 0;
                }
                //保险费
                $where_premium['b.shop_id'] = $v['shop_id'];
                $premium_price = Db::name('order_info')
                    ->field('sum(a.price) as premium_price')
                    ->alias('a')
                    ->join('qu_order_achievement b','b.order_id = a.order_id', 'left')
                    ->where($where_premium)
                    ->where('a.is_seel',1)
                    ->select()
                    ->toarray();
                $v['premium_price'] = $premium_price[0]['premium_price']??0;
                //盈利
                $v['profit'] = number_format((float)$v['total_price'] - (float)$v['advertising'] - (float)$v['estimate_freight'] - (float)$v['material'] - (float)$v['refund'] - (float)$v['PlatformCommission'] - (float)$v['premium_price'], 2, '.', '');

                //计算ROI
                $advertising_total += (float)$v['advertising'];
                $total_price_total += (float)$v['total_price'];
                $quantity_total += (float)$v['quantity'];
                $skuquantity_total += (float)$v['skuquantity'];
                $v['skuquantity'] = (int)$v['skuquantity'];

                //客单价 = 店铺每日总销售额/店铺的每日单量
                $v['customer_price'] = number_format((float)$v['total_price'] / (float)$v['quantity'], 2, '.', '');
                // 客单件 = 店铺每日SKU件数/店铺的每日单量
                $v['customer_sku'] = number_format((float)$v['skuquantity'] / (float)$v['quantity'], 2, '.', '');
            }
            //广告费

            //客单价 = 店铺每日总销售额/店铺的每日单量
            $customer_price_total = number_format((float)$total_price_total / (float)$quantity_total, 2, '.', '');
            // 客单件 = 店铺每日SKU件数/店铺的每日单量
            $customer_sku_total = number_format((float)$skuquantity_total / (float)$quantity_total, 2, '.', '');

            $lists = array_merge($lists,$list_advs);
        }else{
            $lists = $list_advs;
        }
        

        //计算ROI
        if ($advertising_total > 0.001 && $total_price_total > 0.001) {
            $roi_total = number_format((float)$total_price_total / (float)$advertising_total, 2, '.', '');
        }else{
            $roi_total = 0;
        }
            
        //检查广告账户分配是否正常
        $advertising = Db::name('advertising')
            ->field('
                sum(spend) as spend
            ')
            ->where($where_total)
            ->select()
            ->toarray();

        $IntervalDays = ceil(($endOfYesterday - $midnightYesterday) / 3600 / 24);

        $advertising_total_real = $advertising[0]['spend'] ?? 0;
        if(!$lists){
            $advertising_total_real = 0;
        }
        $advertising_total_status = 1;
        $euroToUsdRate = getRes($euroToUsdRate);
        $data = [
            'advertising_total_status' =>  $advertising_total_status,
            'advertising_total' =>  getRes($advertising_total),
            'advertising_total_real' =>  getRes($advertising_total_real),
            'roi' =>  $roi_total,
            'customer_price_total' =>  $customer_price_total,
            'customer_sku_total' =>  $customer_sku_total,
            'list' => $lists,
            'euroToUsdRate' => $euroToUsdRate,
            'total' => $list->total(),
            'midnightYesterday' => $midnightYesterday,
            'endOfYesterday' => $endOfYesterday,
            'IntervalDays' => $IntervalDays,
        ];
        return $data;
    }

    /**
     * @author  zhangheng
     */
    public function exportReportDay($params)
    {
        $lists = [];
        if (empty($params['IntervalDays'])) {
            return json(DefaultResponse(200, false, '日期参数错误'));
        }
        if (empty($params['midnightYesterday']) || empty($params['endOfYesterday'])) {
            return json(DefaultResponse(200, false, '日期筛选错误'));
        }
        if ($params['midnightYesterday'] >= $params['endOfYesterday']) {
            return json(DefaultResponse(200, false, '日期筛选错误!'));
        }
        for ($i = 1; $i <= $params['IntervalDays']; $i++) {
            $data = [
                'shop_id' => $params['shop_id'],
                'midnightYesterday' => $params['midnightYesterday'] + 86400 * ($i - 1),
                'endOfYesterday' => $params['midnightYesterday'] + 86400 * $i - 1,
            ];
            $list = $this->RealTimeTodayInfos($data);
            if(!empty($list)){
                $lists = array_merge($lists, $list);
            }
        }
        
        $header = [];
        $header[] = ['日期', 'midnightYesterday'];
        $header[] = ['销售额', 'total_price'];
        $header[] = ['单量', 'quantity'];
        $header[] = ['SKU件数', 'skuquantity'];
        $header[] = ['客单价', 'customer_price'];
        $header[] = ['客单件', 'customer_sku'];
        $header[] = ['ROI', 'roi'];
        $header[] = ['预估盈利$', 'profit'];
        $header[] = ['打包费$', 'LogisticsMarkupFee'];
        $header[] = ['预估物流$', 'estimate_freight'];
        $header[] = ['采购成本$', 'material'];
        $header[] = ['保险费$', 'premium_price'];
        $header[] = ['平台佣金$', 'PlatformCommission'];
        $header[] = ['预估退款$', 'refund'];
        
        $tableName = "exportReportDay";

        $fileName = "export_" . $tableName . "_" . time();
        return Excel::exportData($lists, $header, $fileName, 'xlsx');

    }

    /**
     * @desc    实时记录今天业绩
     * @date    2024/7/16
     * @author  zhangheng
     */
    public function RealTimeTodayInfo($params)
    {
        $lists = [];
        if (empty($params['IntervalDays'])) {
            return json(DefaultResponse(200, false, '日期参数错误'));
        }
        if (empty($params['midnightYesterday']) || empty($params['endOfYesterday'])) {
            return json(DefaultResponse(200, false, '日期筛选错误'));
        }
        if ($params['midnightYesterday'] >= $params['endOfYesterday']) {
            return json(DefaultResponse(200, false, '日期筛选错误!'));
        }
        for ($i = 1; $i <= $params['IntervalDays']; $i++) {
            $data = [
                'shop_id' => $params['shop_id'],
                'midnightYesterday' => $params['midnightYesterday'] + 86400 * ($i - 1),
                'endOfYesterday' => $params['midnightYesterday'] + 86400 * $i - 1,
            ];
            $list = $this->RealTimeTodayInfos($data);
            if(!empty($list)){
                $lists = array_merge($lists, $list);
            }
        }
        return json(DefaultResponse(200, true, '获取成功', $lists));
    }
    /**
     * @desc    实时记录今天业绩
     * @date    2024/7/16
     * @author  zhangheng
     */
    public function RealTimeTodayInfos($params)
    {
        $shop_id = $params['shop_id'] ??  0;
        $midnightYesterday = $params['midnightYesterday'] ??  0;
        $endOfYesterday = $params['endOfYesterday'] ??  0;

        $where = [];
        $where_campaign = [];
        $where_total = [];
        $where_premium = [];

        //德国汇率
        $ApiOrderLogic = new ApiOrderLogic();
        $euroToUsdRate = $ApiOrderLogic->RealTimeExchangeRateInquiry($params);

        $where[]  = ['a.createtime', '>=', $midnightYesterday];
        $where[]  = ['a.createtime', '<=', $endOfYesterday]; 
        $where_premium[]  = ['b.createtime', '>=', $midnightYesterday];
        $where_premium[]  = ['b.createtime', '<=', $endOfYesterday]; 

        $where_campaign[] = ['a.stat_time', '>=', $midnightYesterday];
        $where_campaign[] = ['a.stat_time', '<=', $endOfYesterday];

        $where_total[]  = ['stat_time', '>=', $midnightYesterday];
        $where_total[]  = ['stat_time', '<=', $endOfYesterday]; 

        $where[]  = ['a.financial_status', 'in', ["partially_paid", "paid", "partially_refunded", "refunded"]];

        $where[] = ['a.shop_id', '=', $shop_id];

        $lists = Db::name('order_achievement')
            ->field('
                COUNT(a.id) as quantity,
                sum(a.total_price) as total_price,
                sum(a.old_total_price) as old_total_price,
                a.shop_id,
                a.currency,
                p.admin_id,
                p.name as shop_name,
                sum(a.skuquantity) as skuquantity,
                sum(a.freight) as freight,
                sum(a.estimate_freight) as estimate_freight,
                sum(a.material) as material
            ')
            ->alias('a')
            ->leftJoin('shops p', 'p.id = a.shop_id')
            ->where($where)
            ->group('a.shop_id')
            ->select()->toarray();
        
        //汇率-----使用最新的汇率
        $order_exchange = Db::name('order_exchange')->order('date desc')->find();

        //面板广告费
        $where_campaign[] = ['a.spend', '>=', 0.001];  

        if($lists){
            foreach ($lists as &$v) {
                //打包费
                $v['LogisticsMarkupFee'] = getRes($v['quantity']*$order_exchange['LogisticsMarkupFee']);

                $v['total_price'] = $v['old_total_price'];

                switch ($v['currency']) {
                    case 'USD':
                        break;
                    case 'EUR':
                        $v['total_price'] = (float)number_format((float)$v['total_price'] * (float)$euroToUsdRate, 2, '.', '');
                        break;
                    default:
                }
                $v['estimate_freight'] = getRes($v['estimate_freight']/$order_exchange['dollar']);
                //预估物流--人民币转美金
                $v['freight']  =  getRes($v['freight']/$order_exchange['dollar']);
                //预估采购
                $v['material'] =  getRes($v['material']/$order_exchange['dollar']);
                //广告费
                $v['advertising'] = $this->advertising_campaign($where_campaign,$v['shop_id']);
                //退款
                $v['refund'] = (float)number_format((float)$v['total_price'] * 0.03, 2, '.', '');
                //平台佣金(预估6个点)
                $v['PlatformCommission'] = (float)number_format((float)$v['total_price'] * 0.06, 2, '.', '');
                //ROI
                if ($v['advertising'] > 0.001 && $v['total_price'] > 0.001) {
                    $v['roi'] = number_format((float)$v['total_price'] / (float)$v['advertising'], 2, '.', '');
                }else{
                    $v['roi'] = 0;
                }
                //保险费
                $where_premium['b.shop_id'] = $v['shop_id'];
                $premium_price = Db::name('order_info')
                    ->field('sum(a.price) as premium_price')
                    ->alias('a')
                    ->join('qu_order_achievement b','b.order_id = a.order_id', 'left')
                    ->where($where_premium)
                    ->where('a.is_seel',1)
                    ->select()
                    ->toarray();
                $v['premium_price'] = $premium_price[0]['premium_price']??0;
                //盈利
                $v['profit'] = number_format((float)$v['total_price'] - (float)$v['advertising'] - (float)$v['estimate_freight'] - (float)$v['material'] - (float)$v['refund'] - (float)$v['PlatformCommission'] - (float)$v['premium_price'], 2, '.', '');

                $v['skuquantity'] = (int)$v['skuquantity'];

                //客单价 = 店铺每日总销售额/店铺的每日单量
                $v['customer_price'] = number_format((float)$v['total_price'] / (float)$v['quantity'], 2, '.', '');
                // 客单件 = 店铺每日SKU件数/店铺的每日单量
                $v['customer_sku'] = number_format((float)$v['skuquantity'] / (float)$v['quantity'], 2, '.', '');

                $v['midnightYesterday'] = date('m月d日', $midnightYesterday);
                
            }
            return $lists;
        }else{
            return [];
        }
    }


    /**
     * @desc   历史数据列表
     * @author luckyXiong
     * @date   2024-11-06
     */
    private function get_admin_name($admin_ids){
        if(!$admin_ids)  return '';
        //批量获取运营名称;
        $admin_names     = Db::name('system_admin')->whereIn('id',$admin_ids)->column('username');
        return $admin_names ? implode(',',$admin_names) : '';

    }


    /**
    * 广告花费
    * @param array params
    */
    public function advertising_real_list($params)
    {
        //实时时间搜索
        $date = $params['date'] ?? "";
        $customDate = $params['customDate'] ?? 0;

        $where_campaign = [];
        //customDate优先
        if(!empty($customDate)){
            switch ($customDate) {
                case '1':
                    //实时
                    $yesterday = date('Y-m-d', time() - timezone());  
                    $midnightYesterday = strtotime($yesterday . ' 00:00:00');
                    $endOfYesterday  = strtotime($yesterday . ' 23:59:59');
                    $where_campaign[] = ['a.stat_time', '>=', $midnightYesterday];
                    $where_campaign[] = ['a.stat_time', '<=', $endOfYesterday];
                    break;
                case '2':
                    $yesterday = date('Y-m-d', strtotime('-1 day') - timezone());
                    $midnightYesterday = strtotime($yesterday . ' 00:00:00');
                    $endOfYesterday  = strtotime($yesterday . ' 23:59:59');
                    $where_campaign[] = ['a.stat_time', '>=', $midnightYesterday];
                    $where_campaign[] = ['a.stat_time', '<=', $endOfYesterday];
                    break;
                case '3':
                    $yesterday = strtotime(date('Y-m-d', time() - timezone()));  
                    $yesterdayEnd = strtotime(date('Y-m-d', strtotime('-6 day') - timezone()));  
                    $where_campaign[] = ['a.stat_time', '>=', $yesterdayEnd];
                    $where_campaign[] = ['a.stat_time', '<=', $yesterday];
                    break;
                default:
                    $yesterday = date('Y-m-d', strtotime('-1 day') - timezone());
                    $midnightYesterday = strtotime($yesterday . ' 00:00:00');
                    $endOfYesterday  = strtotime($yesterday . ' 23:59:59');
                    $where_campaign[] = ['a.stat_time', '>=', $midnightYesterday];
                    $where_campaign[] = ['a.stat_time', '<=', $endOfYesterday];
            }
        }else{
            // 当自定义时间为空时 查日期有没有值
            if (!empty($date)) {
                $where_campaign[] = ['a.stat_time', '>=', strtotime($date['0'])];
                $where_campaign[] = ['a.stat_time', '<=', strtotime($date['1']) + 3600*24-1];
            }else{
                $yesterday = date('Y-m-d', strtotime('-1 day') - timezone());
                $midnightYesterday = strtotime($yesterday . ' 00:00:00');
                $endOfYesterday  = strtotime($yesterday . ' 23:59:59');
                $where_campaign[]  = ['a.stat_time', '>=', $midnightYesterday];
                $where_campaign[]  = ['a.stat_time', '<=', $endOfYesterday]; 
            }
        }

        $lists = Db::name('advertising')
            ->field('a.id,a.spend,a.advertiser_id,b.advertiser_name,d.title')
            ->alias('a')
            ->join('qu_advaccount b', 'b.advertiser_id = a.advertiser_id', 'left')
            ->join('qu_advplatform d', 'd.id = b.platform_id', 'left')
            ->where($where_campaign)
            ->where('spend',">",0)
            ->where('a.shop_id',"=",0)
            ->group('a.id')
            ->order("spend desc")
            ->select()->toarray();
        return json(DefaultResponse(200, true, '获取成功', $lists));
    }
    /**
    * 广告花费
    * @param array params
    */
    public function advertising_campaign($where_campaign,$shop_id)
    {
        $where_campaign[] = ['a.shop_id', '=', $shop_id]; // 店铺id

        $advertiser = Db::name('advertising')
            ->field('sum(a.spend) as spend')
            ->alias('a')
            ->where($where_campaign)
            ->order("spend desc")
            ->find();
        $advertising_spend = 0;
        if (!empty($advertiser['spend'])) {
            $advertising_spend = number_format((float)$advertiser['spend'], 2, '.', '');
        }
        return $advertising_spend;
    }
    

    /**
     * @param $params
     * @return \think\response\Json
     * @throws \think\db\exception\DbException
     * @desc info
     * @author zhangheng
     */
    public function info($params = [])
    {
        $shop_id = $params['shop_id'] ?? 17;

        //实时时间搜索
        $date = $params['date'] ?? "";
        $customDate = $params['customDate'] ?? 0;

        $where_campaign = [];
        $where_campaign[] = ['a.spend', '>=', 0.001];
        $where_campaign[] = ['a.shop_id', '=', $shop_id];

        //customDate优先
        if(!empty($customDate)){
            switch ($customDate) {
                case '1':
                    //实时
                    $yesterday = date('Y-m-d', time() - timezone());  
                    $midnightYesterday = strtotime($yesterday . ' 00:00:00');
                    $endOfYesterday  = strtotime($yesterday . ' 23:59:59');

                    $where_campaign[] = ['a.stat_time', '>=', $midnightYesterday];
                    $where_campaign[] = ['a.stat_time', '<=', $endOfYesterday];

                    break;
                case '2':
                    $yesterday = date('Y-m-d', strtotime('-1 day') - timezone());
                    $midnightYesterday = strtotime($yesterday . ' 00:00:00');
                    $endOfYesterday  = strtotime($yesterday . ' 23:59:59');

                    $where_campaign[] = ['a.stat_time', '>=', $midnightYesterday];
                    $where_campaign[] = ['a.stat_time', '<=', $endOfYesterday];
                    break;
                case '3':
                    $yesterday = strtotime(date('Y-m-d', time() - timezone()));  
                    $yesterdayEnd = strtotime(date('Y-m-d', strtotime('-6 day') - timezone()));

                    $where_campaign[] = ['a.stat_time', '>=', $yesterdayEnd];
                    $where_campaign[] = ['a.stat_time', '<=', $yesterday];

                    break;
                default:
                    $yesterday = date('Y-m-d', strtotime('-1 day') - timezone());
                    $midnightYesterday = strtotime($yesterday . ' 00:00:00');
                    $endOfYesterday  = strtotime($yesterday . ' 23:59:59');

                    $where_campaign[] = ['a.stat_time', '>=', $midnightYesterday];
                    $where_campaign[] = ['a.stat_time', '<=', $endOfYesterday];
            }
        }else{
            // 当自定义时间为空时 查日期有没有值
            if (!empty($date)) {
                $where_campaign[] = ['a.stat_time', '>=', strtotime($date['0'])];
                $where_campaign[] = ['a.stat_time', '<=', strtotime($date['1']) + 3600*24-1];

            }else{
                $yesterday = date('Y-m-d', strtotime('-1 day') - timezone());
                $midnightYesterday = strtotime($yesterday . ' 00:00:00');
                $endOfYesterday  = strtotime($yesterday . ' 23:59:59');

                $where_campaign[]  = ['a.stat_time', '>=', $midnightYesterday];
                $where_campaign[]  = ['a.stat_time', '<=', $endOfYesterday]; 
            }
        }

        $adminId     = $params['adminId']??'';
        
        $advertisings = Db::name('advertising')
            ->field([  
                'sum(a.spend) as spend',  
                'a.advertiser_id',  
                'a.createtime',  
                'avg(a.roas) as roas',
                'sum(a.cpm) as cpm',  
                'sum(a.cpc) as cpc',  
                'a.type_name',  
                'b.platform_id',  
                'b.info',  
                'e.nickname',  
                'b.advertiser_name',  
                'd.title as advplatform_name',
                'a.admin_id'
            ])  
            ->alias('a')
            ->join('qu_advaccount b', 'b.advertiser_id = a.advertiser_id', 'left')
            ->join('qu_advplatform d', 'd.id = b.platform_id', 'left')  
            ->join('qu_system_admin e', 'e.id = a.admin_id', 'left') 
            ->where($where_campaign)
            ->group('a.advertiser_id')  
            ->order("b.platform_id asc,a.admin_id desc,a.roas desc")
            ->select()->toarray();


            

        $advertisingsTotal = Db::name('advertising')
            ->field([  
                'sum(a.spend) as spend',  
                'b.platform_id',  
                'd.title as advplatform_name',
                'a.admin_id'
            ])  
            ->alias('a')
            ->join('qu_advaccount b', 'b.advertiser_id = a.advertiser_id', 'left')
            ->join('qu_advplatform d', 'd.id = b.platform_id', 'left')  
            ->group('a.platform_id')  
            ->where($where_campaign)
            ->order("spend desc")
            ->select()->toarray();

        //update 2025-08-04  小马团队的成员在shecve的数据也展示在他的位置;
        $advertisings_new = [];
        if($adminId==131){
            if(isset($where_campaign[1])){
                unset($where_campaign[1]);
            }

            $shop_18_view = Config('Logisticskey.shop_18');

            $where_campaign[] = ['a.advertiser_id','in',$shop_18_view];

            $advertisings_new = Db::name('advertising')
                ->field([
                    'sum(a.spend) as spend',
                    'a.advertiser_id',
                    'a.createtime',
                    'avg(a.roas) as roas',
                    'sum(a.cpm) as cpm',
                    'sum(a.cpc) as cpc',
                    'a.type_name',
                    'b.platform_id',
                    'b.info',
                    'e.nickname',
                    'b.advertiser_name',
                    'd.title as advplatform_name',
                    'a.admin_id'
                ])
                ->alias('a')
                ->join('qu_advaccount b', 'b.advertiser_id = a.advertiser_id', 'left')
                ->join('qu_advplatform d', 'd.id = b.platform_id', 'left')
                ->join('qu_system_admin e', 'e.id = a.admin_id', 'left')
                ->where($where_campaign)
                ->group('a.advertiser_id')
                ->order("b.platform_id asc,a.admin_id desc,a.roas desc")
                ->select()->toarray();
        }


        if($advertisings_new){
            $advertisings = array_merge($advertisings,$advertisings_new);
        }

        foreach ($advertisings as &$v) {
            $v['createtime'] = date('m-d H:i', $v['createtime']);
            $v['roas'] = number_format((float)$v['roas'], 2, '.', '');
            $v['cpm'] = number_format((float)$v['cpm'], 2, '.', '');
            $v['cpc'] = number_format((float)$v['cpc'], 2, '.', '');
        }


        $return = [
            'advertisingsTotal' => $advertisingsTotal,
            'advertisings' => $advertisings,
        ];
        return json(DefaultResponse(200, true, '获取成功', $return));
    }

    /**
     * @param $params
     * @return \think\response\Json
     * @throws \think\db\exception\DbException
     * @desc skuinfo
     * @author zhangheng
     */
    public function skuinfo($params = [])
    {
        $shop_id = $params['shop_id'] ?? 17;

        //实时时间搜索
        $date = $params['date'] ?? "";
        $customDate = $params['customDate'] ?? 0;

        $where = [];

        //customDate优先
        if(!empty($customDate)){
            switch ($customDate) {
                case '1':
                    //实时
                    $yesterday = date('Y-m-d', time() - timezone());  
                    $midnightYesterday = strtotime($yesterday . ' 00:00:00');
                    $endOfYesterday  = strtotime($yesterday . ' 23:59:59');

                    $where[] = ['createtime', '>=', $midnightYesterday];
                    $where[] = ['createtime', '<=', $endOfYesterday];

                    break;
                case '2':
                    $yesterday = date('Y-m-d', strtotime('-1 day') - timezone());
                    $midnightYesterday = strtotime($yesterday . ' 00:00:00');
                    $endOfYesterday  = strtotime($yesterday . ' 23:59:59');

                    $where[] = ['createtime', '>=', $midnightYesterday];
                    $where[] = ['createtime', '<=', $endOfYesterday];
                    break;
                case '3':
                    $yesterday = strtotime(date('Y-m-d', time() - timezone()));  
                    $yesterdayEnd = strtotime(date('Y-m-d', strtotime('-6 day') - timezone()));

                    $where[] = ['createtime', '>=', $yesterdayEnd];
                    $where[] = ['createtime', '<=', $yesterday];

                    break;
                default:
                    $yesterday = date('Y-m-d', strtotime('-1 day') - timezone());
                    $midnightYesterday = strtotime($yesterday . ' 00:00:00');
                    $endOfYesterday  = strtotime($yesterday . ' 23:59:59');

                    $where[] = ['createtime', '>=', $midnightYesterday];
                    $where[] = ['createtime', '<=', $endOfYesterday];
            }
        }else{
            // 当自定义时间为空时 查日期有没有值
            if (!empty($date)) {
                $where[] = ['createtime', '>=', strtotime($date['0'])];
                $where[] = ['createtime', '<=', strtotime($date['1']) + 3600*24-1];

            }else{
                $yesterday = date('Y-m-d', strtotime('-1 day') - timezone());
                $midnightYesterday = strtotime($yesterday . ' 00:00:00');
                $endOfYesterday  = strtotime($yesterday . ' 23:59:59');

                $where[]  = ['createtime', '>=', $midnightYesterday];
                $where[]  = ['createtime', '<=', $endOfYesterday]; 
            }
        }

        if (!empty($shop_id)) {
            $where[] = ['shop_id', '=', $shop_id];
        }
        
        $where[]  = ['financial_status', 'in', ["partially_paid", "paid", "partially_refunded", "refunded"]];

        $lists = Db::name('order_skuinfo')
            ->field('
                sum(realcount) as skuquantity,
                spu
            ')
            ->where($where)
            ->group('spu')
            ->order('skuquantity desc')
            ->select()->toarray();
        
        //批量获取sku图片;
        $spus  = array_column($lists,'spu');

        //批量获取图片地址;
        $products     = Db::name('product')->field('spu,product_images')->whereIn('spu',$spus)->select()->toArray();
        $products_map = array_column($products,null,'spu');

        foreach ($lists as $k=>$v) {
            $product_images_product       = $products_map[$v['spu']]??[];
            $product_images               = $product_images_product['product_images']??[];
            $display_images               = $product_images ? current(json_decode($product_images)) : '';
            $lists[$k]['product_images']  = $display_images;
        }
        return json(DefaultResponse(200, true, '获取成功', $lists));
    }

    //负责人可以查看的店铺
    public function PersonInCanView($adminId)
    {
        $adminId = $adminId ?? 0;
        $HeadOfBranchOffice = Config('Logisticskey.HeadOfBranchOffice');
        //负责人可以查看的店铺
        if(isset($HeadOfBranchOffice[$adminId]) && !empty($HeadOfBranchOffice[$adminId])){
            return $HeadOfBranchOffice[$adminId]['shop_id'];
        }else{
            //不是负责人 返回空数组
            return [];
        }
    }
    

}
